﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 19 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_ApplicationsEnrollments] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN

	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"EntityType":"AdmissionsRequest"'
			  + ',"EntityID":"' + CAST(AR.EntityID AS NVARCHAR) + '"'
			  + ',"LDHistory":"' + CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"ADDHistory":"' + CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"DisciplineHistory":"' + CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"SpecialMeds":"' + CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"BedWetHistory":"' + CASE WHEN [Bed Wetting History] = 1 THEN 'True' ELSE 'False' END + '"'
			  -- The original question is english first language, so 1 is false and vice versa for the new system
			  + ',"ESL":"' + CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END + '"'
			  + ',"PersonalToilette":"' + CASE WHEN [Personal Toilette] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"MakesBed":"' + CASE WHEN [Makes Bed] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"AppFeePaid":"' + CASE WHEN ADM.[AppFeePaid] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"DesiredGradeLevel":"' + CAST(13-(GradYear-FYAR) AS NVARCHAR) + '"'
			  + ',"DesiredEntryDate":"' + CASE WHEN FTAR = 1 THEN '08/10/' + CAST(FYAR AS nvarchar) 
									 ELSE CASE WHEN FTAR = 2 THEN '12/16/' + CAST(FYAR AS nvarchar)
															 ELSE '04/11/' + CAST(FYAR+1 AS nvarchar) END END + '"'
			  + ',"AdmNotes":"' + COALESCE(CAST([Admission Notes] AS NVARCHAR),'') + '"}' AS JSONvalue,
			  AR.EntityID AS EntityID,'Entity' AS WriteType
	--SELECT *,CAST(13-(GradYear-FYAR) AS NVARCHAR),DesiredGradeLevel,FTAR,FYAR,GradYear,DesiredEntryDate
	FROM 
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR,[Grad-Year] AS GradYear
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
								@NOSQL = ''SELECT AR.*,P.PersonID,AT.* FROM {AdmissionsRequest AS AR} 
									  INNER JOIN {APPLIED AP} ON AR.EntityID = AP.Entity2ID
									  INNER JOIN {Person AS P} ON P.EntityID = AP.Entity1ID
									  INNER JOIN {APPLIEDTO AS AT} ON AT.Entity1ID = AR.EntityID''') AS AR
									  ON ADM.StudentID = AR.PersonID
	WHERE (
		((AR.LDHistory IS NULL AND NOT ADM.[Learning Difficulty History] IS NULL) 
			OR (NOT AR.LDHistory LIKE CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.ADDHistory IS NULL AND NOT ADM.[ADD History] IS NULL) 
			OR (NOT AR.ADDHistory LIKE CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.DisciplineHistory IS NULL AND NOT ADM.[Discipline History] IS NULL) 
			OR (NOT AR.DisciplineHistory LIKE CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.SpecialMeds IS NULL AND NOT ADM.[Special Medication] IS NULL) 
			OR (NOT AR.SpecialMeds LIKE CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.BedWetHistory IS NULL AND NOT ADM.[Bed Wetting History] IS NULL) 
			OR (NOT AR.BedWetHistory LIKE CASE WHEN ADM.[Bed Wetting History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.ESL IS NULL AND NOT ADM.[English First Language] IS NULL) 
			OR (NOT AR.ESL LIKE CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END)) OR
		((AR.PersonalToilette IS NULL AND NOT ADM.[Personal Toilette] IS NULL) 
			OR (NOT AR.PersonalToilette LIKE CASE WHEN ADM.[Personal Toilette] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.MakesBed IS NULL AND NOT ADM.[Makes Bed] IS NULL) 
			OR (NOT AR.MakesBed LIKE CASE WHEN ADM.[Makes Bed] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.AppFeePaid IS NULL AND NOT ADM.[AppFeePaid] IS NULL) 
			OR (NOT AR.AppFeePaid LIKE CASE WHEN ADM.[AppFeePaid] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.DesiredGradeLevel IS NULL AND NOT ADM.GradYear IS NULL AND ADM.GradYear-FYAR > 0) 
			OR (NOT AR.DesiredGradeLevel LIKE CAST(13-(GradYear-FYAR) AS NVARCHAR))) OR
		((AR.DesiredEntryDate IS NULL AND NOT ADM.FYAR IS NULL AND NOT FTAR IS NULL) 
			OR (NOT AR.DesiredEntryDate = CASE WHEN FTAR = 1 THEN CAST('08/10/' + CAST(FYAR AS nvarchar) AS DateTime)
									 ELSE CASE WHEN FTAR = 2 THEN CAST('12/16/' + CAST(FYAR AS nvarchar) AS DateTime)
															 ELSE CAST('04/11/' + CAST(FYAR+1 AS nvarchar) AS DateTime) END END)) OR
		((AR.AdmNotes IS NULL AND NOT ADM.[Admission Notes] IS NULL) 
			OR (NOT AR.AdmNotes LIKE COALESCE(CAST([Admission Notes] AS NVARCHAR),''))) 
	) AND NOT FYAR IS NULL AND NOT FTAR IS NULL

	--PRINT 'Update Application Date'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipname":"' + 'APPLIEDTO' + '"'
			  + ',"Entity1ID":"' + CAST(AR.EntityID AS NVARCHAR) + '"'
			  + ',"Entity2ID":"' + '10000' + '"'
			  + ',"StartDate":"' + CAST(ApplicationDate AS NVARCHAR) + '"'
			  -- If the First Term and First Year are empty, set the end date to one
			  -- month after the start date
			  + CASE WHEN FYAR IS NULL OR FTAR IS NULL
					 THEN ',"EndDate":"' + CAST(DATEADD(month,1,ApplicationDate) AS NVARCHAR) + '"'
				ELSE CASE WHEN EnTerm IS NULL 
					 -- If Enrollment Term is null, this is an active application
					 THEN ''
			  -- Set the end date to the day before the first term the student was/will be here
				ELSE CASE WHEN EnTerm = 1 THEN ',"EndDate":"' 
					+ CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
							THEN CAST(ApplicationDate AS NVARCHAR) ELSE '08/09/' + CAST(EnYear AS nvarchar) END + '"'
				ELSE CASE WHEN EnTerm = 2 THEN ',"EndDate":"' 
					+ CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
							THEN CAST(ApplicationDate AS NVARCHAR) ELSE '12/15/' + CAST(EnYear AS nvarchar) END + '"'
				ELSE ',"EndDate":"' 
					+ CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
							THEN CAST(ApplicationDate AS NVARCHAR) ELSE '04/10/' + CAST(EnYear+1 AS nvarchar) END + '"'
				END END END END
			  + '}' AS JSONvalue,AR.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
								@NOSQL = ''SELECT AR.*,P.PersonID,AT.* FROM {AdmissionsRequest AS AR} 
									  INNER JOIN {APPLIED AP} ON AR.EntityID = AP.Entity2ID
									  INNER JOIN {Person AS P} ON P.EntityID = AP.Entity1ID
									  INNER JOIN {APPLIEDTO AS AT} ON AT.Entity1ID = AR.EntityID''') AS AR
									  ON ADM.StudentID = AR.PersonID
	LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,MIN(Year) AS EnYear,MIN(Term) AS EnTerm FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' AND Term > 0 GROUP BY StudentID') AS ET
									  ON ET.StudentID = ADM.StudentID
	WHERE 
		AR.StartDate <> ApplicationDate
		OR (NOT AR.EndDate IS NULL AND EnTerm IS NULL AND NOT FYAR IS NULL AND NOT FTAR IS NULL)
		OR
		AR.EndDate <> 
		(CASE WHEN FYAR IS NULL OR FTAR IS NULL
					 THEN DATEADD(month,1,ApplicationDate)
		-- Set the end date to the day before the first term the student was/will be here
		ELSE CASE WHEN EnTerm = 1 THEN 
			CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
					THEN ApplicationDate ELSE CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime) END
		ELSE CASE WHEN EnTerm = 2 THEN 
			CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
					THEN ApplicationDate ELSE CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime) END
		ELSE 
			CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					THEN ApplicationDate ELSE CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime) END
		END END END)
	
	--PRINT 'Update/Insert Enrollments'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"STUDENT","Entity2ID":"10000"'
			+ ',"Entity1ID":"' + CAST(AR.EntityID AS NVARCHAR) + '"'
			+ CASE WHEN EnTerm = 1 THEN ',"StartDate":"' 
						+ '08/10/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(EnYear AS nvarchar) + '"'
					ELSE CASE WHEN EnTerm = 2 THEN ',"StartDate":"' 
						+ '12/16/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(EnYear+1 AS nvarchar) + '"'
					ELSE ',"StartDate":"' 
						+ '04/11/' + CAST(EnYear+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(EnYear+1 AS nvarchar) + '"'
					END END
			
			+'}' AS JSONvalue,AR.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT StudentID,Year AS EnYear,Term AS EnTerm 
									 FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' 
									 AND Term > 0') AS ET
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS AR
									  ON ET.StudentID = AR.PersonID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Student}''') AS ST
									  ON ST.Entity1ID = AR.EntityID AND (
		  ST.Startdate = CASE WHEN EnTerm = 1 THEN CAST('08/10/' + CAST(EnYear AS nvarchar) AS DateTime)
					ELSE CASE WHEN EnTerm = 2 THEN CAST('12/16/' + CAST(EnYear AS nvarchar) AS DateTime)
											  ELSE CAST('04/11/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					END END
		 AND ST.EndDate = CASE WHEN EnTerm = 1 THEN CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
					ELSE CASE WHEN EnTerm = 2 THEN CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
											  ELSE CAST('07/25/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					END END)
	WHERE ST.Entity1ID IS NULL

	--PRINT 'Remove no longer valid enrollments'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)	
	SELECT '{"RequestedAction":"Delete","DeleteOverride":"True",'
			+ '"ShortRelationshipName":"STUDENT","Entity2ID":"10000"'
			+ ',"Entity1ID":"' + CAST(AR.EntityID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(StartDate AS NVARCHAR) + '"'
			+ ',"EndDate":"' + CAST(EndDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,AR.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Person}''') AS AR
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL
									 @NOSQL = ''SELECT * FROM {Student}''') AS ST
									  ON ST.Entity1ID = AR.EntityID
 
	LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,Year AS EnYear,Term AS EnTerm 
									 FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' 
									 AND Term > 0') AS ET									  
									  ON ET.StudentID = AR.PersonID
									   AND (
		  ST.Startdate = CASE WHEN EnTerm = 1 THEN CAST('08/10/' + CAST(EnYear AS nvarchar) AS DateTime)
					ELSE CASE WHEN EnTerm = 2 THEN CAST('12/16/' + CAST(EnYear AS nvarchar) AS DateTime)
											  ELSE CAST('04/11/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					END END
		 AND ST.EndDate = CASE WHEN EnTerm = 1 THEN CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
					ELSE CASE WHEN EnTerm = 2 THEN CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
											  ELSE CAST('07/25/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					END END)
	WHERE ET.StudentID IS NULL



	RETURN 
END